package com.ssy.lingxi.product.repository.commodity;

import com.ssy.lingxi.product.entity.commodity.Commodity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * 商品持久化层
 * @author jw.chen
 * @version 2.0.0
 * @date 2020/6/28
 */
public interface CommodityRepository extends JpaRepository<Commodity,Long>, JpaSpecificationExecutor<Commodity> {

    List<Commodity> findAllByMemberIdAndMemberRoleIdAndIdIn(Long memberId, Long memberRoleId, List<Long> idList);

    List<Commodity> findByMemberIdAndMemberRoleIdAndNameAndIdNot(Long memberId, Long memberRoleId, String name, Long id);

    List<Commodity> findByMemberIdAndMemberRoleIdAndName(Long memberId, Long memberRoleId, String name);

    List<Commodity> findByMemberIdAndMemberRoleIdAndCustomerCategoryId(Long memberId, Long memberRoleId, Long customerCategoryId);

    List<Commodity> findByMemberIdAndMemberRoleIdAndCustomerCategoryIdAndCommodityAttributeListCustomerAttributeIdIn(Long memberId, Long memberRoleId, Long customerCategoryId, List<Long> attributeIdList);

    List<Commodity> findByBrandId(Long brandId);

    Boolean existsByBrandId(Long brandId);

    Boolean existsByUnitId(Long unitId);

    Boolean existsByMemberIdAndMemberRoleIdAndName(Long memberId, Long memberRoleId, String name);

    List<Commodity> findByMemberIdAndMemberRoleId(Long memberId, Long memberRoleId);

    List<Commodity> findByMemberIdAndMemberRoleIdAndImportBatch(Long memberId, Long memberRoleId, String importBath);

    List<Commodity>  findByMemberIdAndMemberRoleIdAndImportBatchContaining(Long memberId, Long memberRoleId, String importBath);

    @Query(value = "select count(*) from product_commodity_attribute_value pcavm where pcavm.customer_attribute_value_id = ?1",nativeQuery = true)
    Integer findCommodityCountByCustomerAttributeValueId(Long customerAttributeValueId);

    @Query(value = "select count(*) from product_commodity where logistics ->> 'templateId' = :templateId", nativeQuery = true)
    Integer findCommodityCountByLogisticsTemplateId(@Param("templateId") String templateId);

    @Query(value = "select count(*) from product_commodity where logistics ->> 'sendAddress' = :sendAddress", nativeQuery = true)
    Integer findCommodityCountByLogisticsSendAddressId(@Param("sendAddress") String sendAddress);

    @Query(value = "select count(*) from product_commodity where logistics ->> 'company' = :company", nativeQuery = true)
    Integer findCommodityCountByLogisticsCompanyId(@Param("company") String company);

    List<Commodity> findAllByCustomerCategoryIdIn(List<Long> customerCategoryIdList);

    List<Commodity> findByCustomerCategoryIdAndBrandIdIn(Long customerCategoryId,List<Long> brandIdList);

    @Query(value = "\tselect * from \n" +
            "\tproduct_commodity \n" +
            "\tleft join product_customer_category \n" +
            "\ton customer_category_id = product_customer_category.id\n" +
            "\tleft join product_category\n" +
            "\ton product_customer_category.category_id = product_category.id\n" +
            "\tleft join product_category_attribute \n" +
            "\ton product_category_attribute.category_id = product_category.id\n" +
            "\tleft join product_attribute \n" +
            "\ton product_category_attribute.attribute_id = product_attribute.id\n" +
            "\twhere \n" +
            "\tproduct_category.id in (:categoryIdList)\n" +
            "\tand \n" +
            "\tproduct_attribute.id in (:attributeIdList)",nativeQuery = true)
    List<Commodity> findAllByCategoryIdInAndAttributeIdIn(@Param("categoryIdList") List<Long> categoryIdList,@Param("attributeIdList") List<Long> attributeIdList);

    @Query(value = "select \n" +
            "distinct brand_id \n" +
            "from product_commodity \n" +
            "where \n" +
            "customer_category_id \n" +
            "in (\n" +
            "select \n" +
            "category_id \n" +
            "from \n" +
            "product_customer_category_attribute \n" +
            "where attribute_id \n" +
            "in (:customerAttributeIdList) \n" +
            "and \n" +
            "category_id in (:customerCategoryIdList))",nativeQuery = true)
    List<Long> findBrandIdListWithCondition(@Param("customerAttributeIdList")List<Long> customerAttributeIdList,@Param("customerCategoryIdList") List<Long> customerCategoryIdList);

    @Query(value = "select \n" +
            "distinct brand_id \n" +
            "from product_commodity \n" +
            "where \n" +
            "customer_category_id in (:customerCategoryIdList)",nativeQuery = true)
    List<Long> findBrandIdListWithCondition(@Param("customerCategoryIdList") List<Long> customerCategoryIdList);

//    @Query(value = "select \n" +
//            "distinct brand_id \n" +
//            "from product_commodity \n" +
//            "where \n" +
//            "customer_category_id \n" +
//            "in (\n" +
//            "select \n" +
//            "category_id \n" +
//            "from \n" +
//            "product_customer_category_attribute \n" +
//            "where attribute_id \n" +
//            "in (select attribute_id from product_customer_category_attribute where category_id in (:customerCategoryIdList))\n" +
//            "and\n" +
//            "category_id in (:customerCategoryIdList))",nativeQuery = true)
//    List<Long> findBrandIdList(@Param("customerCategoryIdList")List<Long> customerCategoryIdList);

}
